

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    
    <title>RWC Converter v1.0 documentation</title>
    <link rel="stylesheet" href="_static/default.css" type="text/css" />
    <link rel="stylesheet" href="_static/pygments.css" type="text/css" />
    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    '',
        VERSION:     '1.0',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true
      };
    </script>
    <script type="text/javascript" src="_static/jquery.js"></script>
    <script type="text/javascript" src="_static/underscore.js"></script>
    <script type="text/javascript" src="_static/doctools.js"></script>
    <link rel="top" title="None" href="#" /> 
  </head>
  <body>
    <div class="related">
      <h3>Navigation</h3>
      <ul>
        <li><a href="#">RWC Converter v1.0 documentation</a> &raquo;</li> 
      </ul>
    </div>  

    <div class="document">
      <div class="documentwrapper">
        <div class="bodywrapper">
          <div class="body">
            
  <div class="section" id="robert-s-configuration-management-documentation">
<h1>Robert&#8217;s configuration management documentation<a class="headerlink" href="#robert-s-configuration-management-documentation" title="Permalink to this headline">¶</a></h1>
<p>This documentation describes the use of the migration utility I designed for migrating
configuration management data from Microsoft Word documents to other, more manageable formats.
It also contains recommendations regarding future data acquisition during the plant documentation
project.  If necessary, I can be contacted at <a class="reference external" href="mailto:rwcarlsen&#37;&#52;&#48;gmail&#46;com">rwcarlsen<span>&#64;</span>gmail<span>&#46;</span>com</a> . I can provide a copy of the
migration utility if you lose track of it.</p>
<div class="section" id="migration-from-word">
<h2>Migration from Word<a class="headerlink" href="#migration-from-word" title="Permalink to this headline">¶</a></h2>
<div class="toctree-wrapper compound">
<span id="document-preparation"></span><div class="section" id="preparing-for-migration">
<span id="get-started"></span><h3>Preparing for Migration<a class="headerlink" href="#preparing-for-migration" title="Permalink to this headline">¶</a></h3>
<p><em>Because some steps of the document migration process require a Windows machine,
this manual is directed toward the Windows platform.
This page will outline the setup and use of the tools required for the migration project.
The actual steps and procedures of the migration process are described on the</em>
<a class="reference internal" href="index.html#the-migration-process"><em>Migration Process</em></a> page.</p>
<p>Minimally, the python interpreter must be installed in order to run this migration utility.
This utility was developed and tested on <strong>Python 2.7.1</strong> and is only guaranteed to work properly
with that specific version (other 2.7.x versions should be fine). Python is an interpreted, scripting
programming language with an extensive standard library and lent itself well to this project.
The basic process for downloading and installing python is outlined
below.  There are two other frameworks (<a class="reference internal" href="index.html#install-django"><em>Django</em></a> and <a class="reference internal" href="index.html#install-sphinx"><em>Sphinx</em></a>)
that can be added to the python installation that allow
the utility to manipulate data in some useful ways.</p>
<p>This migragion utility is designed to be run from the
command-prompt (cmd.exe).  The command prompt can be started by clicking start menu and then clicking run.
Type cmd.exe into the text box and click okay. Use the <tt class="docutils literal"><span class="pre">cd</span></tt> command to change
the current working directory as desired.</p>
<div class="section" id="installing-and-using-python">
<span id="install-python"></span><h4>Installing and Using Python<a class="headerlink" href="#installing-and-using-python" title="Permalink to this headline">¶</a></h4>
<p>First, download the appropriate installer for your computer from the <a class="reference external" href="http://www.python.org/getit/106">Python downloads page</a>.
Follow the steps and complete the setup operation.
The Python installer does not add the interpreter to the command prompt PATH environment variable.
On Windows XP, this is done as follows:</p>
<blockquote>
<div><ol class="arabic">
<li><p class="first">Right-click on My Computer and select &#8216;Properties&#8217;.</p>
</li>
<li><p class="first">In the &#8216;System Properties&#8217; windows select the &#8216;Advanced&#8217; tab.</p>
</li>
<li><p class="first">Towards the bottom of the window, click the &#8216;Environment Variables&#8217; button.</p>
</li>
<li><p class="first">In the &#8216;Environment Variables&#8217; window under the &#8216;User variables for xxxxx&#8217;
section, do either &#8216;a&#8217; or &#8216;b&#8217; below:</p>
<ol class="loweralpha">
<li><p class="first">If There is no variable named &#8216;PATH&#8217; in the listbox</p>
<blockquote>
<div><ol class="lowerroman simple">
<li>Click the &#8216;New&#8217; button.</li>
<li>In the &#8216;Edit User Variable&#8217; window type <tt class="docutils literal"><span class="pre">PATH</span></tt> in the &#8216;Variable name&#8217; textbox.</li>
<li>Type <tt class="docutils literal"><span class="pre">C:\path\to\python\installation</span></tt> (<tt class="docutils literal"><span class="pre">C:\Python27</span></tt> by default).</li>
</ol>
</div></blockquote>
</li>
<li><p class="first">If there is, select it and then click &#8216;Edit&#8217;.</p>
<blockquote>
<div><ol class="lowerroman simple">
<li>Select the &#8216;PATH&#8217; variable and click the &#8216;Edit&#8217; button.</li>
<li>Add a semi-colon to the end of the text in the &#8216;Variable value&#8217; textbox followed
by <tt class="docutils literal"><span class="pre">C:\path\to\python\installation</span></tt> (<tt class="docutils literal"><span class="pre">C:\Python27</span></tt> by default).</li>
</ol>
</div></blockquote>
</li>
</ol>
</li>
<li><p class="first">Click okay, okay, okay.</p>
</li>
</ol>
</div></blockquote>
<p>The Python interpreter should now be on your command prompt PATH. You can run the interpreter or python scripts by
typing <tt class="docutils literal"><span class="pre">python</span></tt> in the command prompt (cmd.exe). For Example:</p>
<div class="highlight-python"><pre>cd \path\to\my\script
python my-script.py myarg1 ...</pre>
</div>
<p id="install-django">After installing the Python interpreter and other desired packages (e.g. Django and Sphinx),
you are ready to begin data extraction and conversion to other formats. Use of the migration utility
is described on the <a class="reference internal" href="index.html#the-migration-process"><em>Migration Process</em></a> page.</p>
<div class="section" id="adding-django-to-python">
<h5>Adding Django to Python<a class="headerlink" href="#adding-django-to-python" title="Permalink to this headline">¶</a></h5>
<p>Adding Django will provide functionality for directly inserting the data into
the following supported relational databases: PostgreSQL, MySQL, SQLite, and Oracle.
Tables and relationships reflecting the data structure will automatically be created.
This project was developed and tested using <strong>Django version 1.3</strong>.</p>
<p>Follow these steps to add the Django package to python:</p>
<blockquote>
<div><ol class="arabic">
<li><p class="first">First, download the package from the <a class="reference external" href="https://www.djangoproject.com/download/">Django downloads page</a>.
Last I checked, click the link &#8216;Django-1.3.tar.gz&#8217; under the &#8216;Option 1&#8217; section.</p>
</li>
<li><p class="first">Use winzip, 7z, or another utility to extract the compressed django directory and files.</p>
</li>
<li><p class="first">In a command prompt (cmd.exe) change directory to the extracted Django folder.</p>
</li>
<li><p class="first">Run the command:</p>
<div class="highlight-python"><pre>python setup.py install</pre>
</div>
</li>
</ol>
</div></blockquote>
</div>
<div class="section" id="adding-sphinx-to-python">
<span id="install-sphinx"></span><h5>Adding Sphinx to Python<a class="headerlink" href="#adding-sphinx-to-python" title="Permalink to this headline">¶</a></h5>
<p>Same process as python.  This is only used for outputting the data to a restructuredText Sphinx project.
This output format was only created for my convenience during development.</p>
</div>
</div>
</div>
<span id="document-the_conversion_process"></span><div class="section" id="the-migration-process">
<span id="id1"></span><h3>The Migration Process<a class="headerlink" href="#the-migration-process" title="Permalink to this headline">¶</a></h3>
<p>There are a few discrete steps involved in the migration of data away from the
Microsoft Word documents.  The ability is provided to write the data into 3 distinct
formats (a delimited text file, XML files, and restructuredText).  The data can also
optionallyl be directly inserted into a relational database such as MySQL. The migration steps are:</p>
<blockquote>
<div><ol class="arabic simple">
<li><a class="reference internal" href="index.html#config-constants"><em>Specify settings</em></a> for the migration.</li>
<li><a class="reference internal" href="index.html#doc-to-docx"><em>Convert files</em></a> from doc to docx.</li>
<li><a class="reference internal" href="index.html#stripping-data"><em>Extract data</em></a> from docx files (called &#8216;stripping&#8217;).</li>
<li><a class="reference internal" href="index.html#other-formats"><em>Write data</em></a> to desired format.</li>
</ol>
</div></blockquote>
<p>All three steps are executed by running the &#8216;main.py&#8217; script in the project root directory.
The script must be run using the python interpreter through the command prompt
(see <a class="reference internal" href="index.html#get-started"><em>Preparing for Migration</em></a>). The script uses settings from the &#8216;constants.py&#8217;
file to determine appropriate directories and file names for the migration. Each time the
script is executed, a single command line argument must be provided. Arguments for the script include:</p>
<blockquote>
<div><ul class="simple">
<li><a class="reference internal" href="index.html#doc-to-docx"><em>convert</em></a></li>
<li><a class="reference internal" href="index.html#stripping-data"><em>strip</em></a></li>
<li><a class="reference internal" href="index.html#delim-file"><em>delim-db</em></a></li>
<li><a class="reference internal" href="index.html#xml-files"><em>xml-db</em></a></li>
<li><a class="reference internal" href="index.html#xml-files"><em>xml-files</em></a></li>
<li><a class="reference internal" href="index.html#db-direct"><em>database-direct</em></a></li>
<li><a class="reference internal" href="index.html#rst-output"><em>rst-index</em></a></li>
</ul>
</div></blockquote>
<p>Assuming the Python interpreter is on your system PATH, running the script can be as simple as shown here:</p>
<div class="highlight-python"><pre>python main.py [command]</pre>
</div>
<p>If the current working directory of the command prompt is not the project folder,
<tt class="docutils literal"><span class="pre">main.py</span></tt> above would need to be replaced with <tt class="docutils literal"><span class="pre">\path\to\project\folder\main.py</span></tt>.</p>
<p><em>* Before running any of the procedures described on this page in the command prompt,
the current working directory should be set to the root directory of the migration utility.</em></p>
<div class="section" id="configuring-constants-py">
<span id="config-constants"></span><h4>Configuring constants.py<a class="headerlink" href="#configuring-constants-py" title="Permalink to this headline">¶</a></h4>
<p>The &#8216;constants.py&#8217; file is located in the project root directory. There are several settings
variables contained within a python class of the form <tt class="docutils literal"><span class="pre">[setting_name]</span> <span class="pre">=</span> <span class="pre">'[value]'</span></tt>.  All paths
should be absolute paths (with one exception).  In general, forward slashes should be used when
entering paths.  If back slashes are used, each backslash must be escaped with a backslash
(i.e. <tt class="docutils literal"><span class="pre">\my\absolute\path</span></tt> should be entered as <tt class="docutils literal"><span class="pre">\\my\\absolute\\path</span></tt>). After the variables
have all been given appropriate values, the migration process can begin. A view of the &#8216;constants.py&#8217;
file and a description of the important variables are shown below.  The settings variables will be
referred to throughout this page - often without explicitly identification as a settings variable.</p>
<blockquote>
<div><p><strong>Sample &#8216;constants.py&#8217; file</strong>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c">#!/usr/local/bin/python2.7</span>
<span class="kn">import</span> <span class="nn">os</span>

<span class="k">class</span> <span class="nc">Constants</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
    <span class="n">pile_root</span> <span class="o">=</span> <span class="s">&#39;</span><span class="se">\\\\</span><span class="s">fsisc1</span><span class="se">\\</span><span class="s">projects</span><span class="se">\\</span><span class="s">LIFEEXT</span><span class="se">\\</span><span class="s">atr component database&#39;</span>
    <span class="n">db_root</span> <span class="o">=</span> <span class="s">&#39;C:/docx-final&#39;</span>
    <span class="n">dst_root</span> <span class="o">=</span> <span class="s">&#39;C:/converted-final&#39;</span>
    <span class="n">wordconv_path</span> <span class="o">=</span> <span class="s">&#39;C:/cygwin/home/CALSRW/atr-indexer/resources/wordconv&#39;</span>

    <span class="n">logs_dir</span> <span class="o">=</span> <span class="s">&#39;C:/rwc-logs-final&#39;</span>
    <span class="n">rst_index_dir</span> <span class="o">=</span> <span class="s">&#39;C:/comp-index&#39;</span>

<span class="c">############### shouldn&#39;t need  modification below ###############</span>
    <span class="n">dbase_name</span> <span class="o">=</span> <span class="s">&#39;dbase.pickle&#39;</span>

    <span class="n">temp_files_dir</span> <span class="o">=</span> <span class="n">os</span><span class="o">.</span><span class="n">path</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">logs_dir</span><span class="p">,</span> <span class="s">&#39;temp&#39;</span><span class="p">)</span>
    <span class="n">pics_dir</span> <span class="o">=</span> <span class="n">os</span><span class="o">.</span><span class="n">path</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">logs_dir</span><span class="p">,</span> <span class="s">&#39;pics&#39;</span><span class="p">)</span>
    <span class="n">parsed_files_dir</span> <span class="o">=</span> <span class="n">dst_root</span>

    <span class="n">tagdict_filepath</span> <span class="o">=</span> <span class="s">&#39;keys.xml&#39;</span>
</pre></div>
</div>
<p><strong>Settings variables</strong>:</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="13%" />
<col width="87%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head">Name</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr><td>pile_root</td>
<td>path to the root directory containing all original Microsoft Word documents</td>
</tr>
<tr><td>db_root</td>
<td>path to the root directory where all Microsoft Word documents will be placed after conversion to docx format</td>
</tr>
<tr><td>dst_root</td>
<td>path to root directory where xml files are stored (if converting to xml format)</td>
</tr>
<tr><td>wordconv_path</td>
<td>path to the &#8216;wordconv.exe&#8217; utility (should be located at <tt class="docutils literal"><span class="pre">[path-to-project]/resources/wordconv</span></tt>)</td>
</tr>
<tr><td>logs_dir</td>
<td>directory where logs and the serialized (pickled) data is stored after stripping from the Word documents</td>
</tr>
<tr><td>rst_index_dir</td>
<td>path where a sphinx project has been created - used for conversion to restructuredText</td>
</tr>
<tr><td>delim_chars</td>
<td>characters used as delimiter for writing data to a delimited file (e.g. csv/comma or otherwise)</td>
</tr>
<tr><td>dbase_name</td>
<td>file name for the serialized (pickled) data after stripping</td>
</tr>
<tr><td>temp_files_dir</td>
<td>temporary directory used to unzip docx data</td>
</tr>
<tr><td>pics_dir</td>
<td>directory where all pictures extracted from Word documents are stored</td>
</tr>
<tr><td>parsed_files_dir</td>
<td>same usage as <tt class="docutils literal"><span class="pre">dst_root</span></tt> (usu. same value as <tt class="docutils literal"><span class="pre">dst_root</span></tt>)</td>
</tr>
<tr><td>tagdict_filepath</td>
<td>(<strong>should be relative path</strong>) path to xml file that contains mappings from Word doc field names to data extraction field names.</td>
</tr>
</tbody>
</table>
</div></blockquote>
</div></blockquote>
</div>
<div class="section" id="doc-to-docx-conversion">
<span id="doc-to-docx"></span><h4>Doc to Docx Conversion<a class="headerlink" href="#doc-to-docx-conversion" title="Permalink to this headline">¶</a></h4>
<p>The actual doc to docx conversion is completed by a Microsoft utility called wordconv.
The migration script is simply a wrapper around the wordconv utility that recursively crawls
through a directory. The conversion handles both &#8216;*.doc&#8217; and &#8216;*.docx&#8217; files appropriately.
&#8216;*.doc&#8217; files are converted to &#8216;*.docx&#8217; and then placed in the specified <tt class="docutils literal"><span class="pre">db_root</span></tt> directory.
&#8216;*.docx&#8217; files are simply copied directly into the specified <tt class="docutils literal"><span class="pre">db_root</span></tt> directory.  To execute the
conversion process, run the &#8216;main.py&#8217; script from the command prompt (cmd.exe) using the
command-line argument <tt class="docutils literal"><span class="pre">convert</span></tt>:</p>
<div class="highlight-python"><pre>python main.py convert</pre>
</div>
<p>To safely terminate the conversion process, press ctrl-c.  If the conversion process terminates
prematurely, restarting it will not reconvert the already converted files. Files that already
exist in the <tt class="docutils literal"><span class="pre">db_root</span></tt> directory will be skipped over and conversion will continue where it left off.
The crawling process does not look at date-modified or date-created meta-data.  If files in the source
(<tt class="docutils literal"><span class="pre">pile_root</span></tt>) directory are modified between conversion runs, the modified file(s) will not be reconverted
and updated in the destination (<tt class="docutils literal"><span class="pre">db_root</span></tt>).</p>
</div>
<div class="section" id="stripping-the-data">
<span id="stripping-data"></span><h4>Stripping the Data<a class="headerlink" href="#stripping-the-data" title="Permalink to this headline">¶</a></h4>
<p>After conversion to docx files, the data can be stripped out and stored in a structured format.
Stripping extracts data and pictures from each Word document.  All pictures are given descriptive, unique
names and placed in a single directory (pics_dir as described above). The data from a single word document
is stored in a Python object (instance of the Component class).  Each object stores data from the word
document in dictionaries (fieldname-value pairs). The object also stores the names of the pictures that
are associated with it.  The collection of all objects representing every word document&#8217;s data
is stored in another umbrella oject (instance of the DBase class).  This object is serialized using python&#8217;s
pickle module and stored as a file named with the value specified by dbase_name (described above).
Custom python scripts can be written relatively easily to manipulate and use this serialized data as desired.</p>
<p>To execute the conversion process, run the &#8216;main.py&#8217; script from the command prompt (cmd.exe) using the
command-line argument <tt class="docutils literal"><span class="pre">strip</span></tt>:</p>
<div class="highlight-python"><pre>python main.py strip</pre>
</div>
<p>To (semi)safely terminate the stripping process, press ctrl-c.  This allows the already processed data and progress
to be saved (to prevent repeating from the beginning). If the conversion process terminates
prematurely, restarting it should not reprocess the already stripped/stored files. However, it is best to complete the
entire stripping process without interruption to ensure a clean stripping (I am only 93% confident in an error
free continuation of a previously interrupted stripping).</p>
</div>
<div class="section" id="outputting-data-to-other-formats">
<span id="other-formats"></span><h4>Outputting Data to Other Formats<a class="headerlink" href="#outputting-data-to-other-formats" title="Permalink to this headline">¶</a></h4>
<p>The serialized data from the stripping process can then be retrieved at will and written to various formats.
Functionality is provided allowing the writing of the data to a few basic formats.  Instructions for writing to
these provided-by-default formats is described in the sections below. Writing the data to other formats has no
effect on the serialized data - it remains intact as originally stripped. Custom python scripts can also be written
relatively easily to manipulate and use the python-serialized (pickled) data as desired (see the &#8216;component.py&#8217; and &#8216;dbase.py&#8217;
source code for this object structure).</p>
<div class="section" id="delimited-file">
<span id="delim-file"></span><h5>Delimited File<a class="headerlink" href="#delimited-file" title="Permalink to this headline">¶</a></h5>
<p>The data can be written to a comma separated values file (csv) or any other delimited file.  The delimiter can be a series of characters of any length.
The default delimiter is &#8216;$&#8217; (chosen because it is not present in any of the word documents). Alternate delimiters can be used by specifying them in the
delim_chars variable of the constants.py file. To write the data to delimited file, run the &#8216;main.py&#8217; script from the command prompt (cmd.exe) using the
command-line argument <tt class="docutils literal"><span class="pre">delim-db</span></tt>:</p>
<div class="highlight-python"><pre>python main.py delim-db</pre>
</div>
<p>The resulting delimited file is written to the directory specified by the <tt class="docutils literal"><span class="pre">logs_dir</span></tt> variable setting
The file is named &#8216;mycmdb.txt&#8217; by default. These defaults can be altered if desired in the &#8216;delimwriter.py&#8217; file.
The resulting delimited file can then be imported into Excel (Excel only supports importing single character
delimited files) or used as desired.</p>
</div>
<div class="section" id="xml-files">
<span id="id2"></span><h5>XML Files<a class="headerlink" href="#xml-files" title="Permalink to this headline">¶</a></h5>
<p>The data can be written to a set of xml files where each component&#8217;s data is stored in a uniquely named xml file.
The collection of xml files are placed into a single directory as specified by the <tt class="docutils literal"><span class="pre">dst_root</span></tt> setting.
To write the data to xml files, run the &#8216;main.py&#8217; script from the command prompt (cmd.exe) using the
command-line argument <tt class="docutils literal"><span class="pre">xml-files</span></tt>:</p>
<div class="highlight-python"><pre>python main.py xml-files</pre>
</div>
<p>The data can also be written to a single xml file that is simply the consolidation of the individual xml files as described above.
The resulting xml file is written to the directory specified by the <tt class="docutils literal"><span class="pre">logs_dir</span></tt> variable setting
The file is named &#8216;mycmdb.xml&#8217; by default. These defaults can be altered if desired in the &#8216;xmldb.py&#8217; file.
The resulting xml file can then be imported into a database, viewed in a browser, or used otherwise.
To write the data to a single, consolidated xml file, run the &#8216;main.py&#8217; script from the command prompt (cmd.exe) using the
command-line argument <tt class="docutils literal"><span class="pre">xml-db</span></tt>:</p>
<div class="highlight-python"><pre>python main.py xml-db</pre>
</div>
</div>
<div class="section" id="direct-database-insertion">
<span id="db-direct"></span><h5>Direct Database Insertion<a class="headerlink" href="#direct-database-insertion" title="Permalink to this headline">¶</a></h5>
<p><em>This functionality requires the django framework to be added to your python installation as
described in</em> <a class="reference internal" href="index.html#install-django"><em>Adding Django</em></a>.</p>
<p>There must be an accessible, supported database server running for this to work.
Supported databases include PostgreSQL, MySQL, SQLite, and Oracle.
The Django framework is used as an abstraction layer for the database.
A set of tables are created in the database with appropriate relationships.
The data for each component is inserted into these tables.</p>
<p>The django files in the migration project reside in <tt class="docutils literal"><span class="pre">[path</span> <span class="pre">to</span> <span class="pre">project]/djangodb</span></tt> directory.
In order to connect to a database server, the &#8216;settings.py&#8217; file in the &#8216;djangodb&#8217; directory
must be configured properly. The section of the file that needs to be edited is shown below.
The ENGINE, NAME, USER, PASSWORD, HOST, and PORT for the database server must be specified.</p>
<p>Django&#8217;s settings.py file:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="o">...</span>
<span class="o">...</span>
<span class="n">DATABASES</span> <span class="o">=</span> <span class="p">{</span>
    <span class="s">&#39;default&#39;</span><span class="p">:</span> <span class="p">{</span>
        <span class="s">&#39;ENGINE&#39;</span><span class="p">:</span> <span class="s">&#39;django.db.backends.mysql&#39;</span><span class="p">,</span> <span class="c"># Add &#39;postgresql_psycopg2&#39;, &#39;postgresql&#39;, &#39;mysql&#39;, &#39;sqlite3&#39; or &#39;oracle&#39;.</span>
        <span class="s">&#39;NAME&#39;</span><span class="p">:</span> <span class="s">&#39;django-fun&#39;</span><span class="p">,</span>                 <span class="c"># Or path to database file if using sqlite3.</span>
        <span class="s">&#39;USER&#39;</span><span class="p">:</span> <span class="s">&#39;root&#39;</span><span class="p">,</span>                       <span class="c"># Not used with sqlite3.</span>
        <span class="s">&#39;PASSWORD&#39;</span><span class="p">:</span> <span class="s">&#39;password&#39;</span><span class="p">,</span>               <span class="c"># Not used with sqlite3.</span>
        <span class="s">&#39;HOST&#39;</span><span class="p">:</span> <span class="s">&#39;&#39;</span><span class="p">,</span>                           <span class="c"># Set to empty string for localhost. Not used with sqlite3.</span>
        <span class="s">&#39;PORT&#39;</span><span class="p">:</span> <span class="s">&#39;&#39;</span><span class="p">,</span>                           <span class="c"># Set to empty string for default. Not used with sqlite3.</span>
    <span class="p">}</span>
<span class="p">}</span>
<span class="o">...</span>
<span class="o">...</span>
</pre></div>
</div>
<p>The values shown above would be correct if connecting to a MySQL server with a database named
&#8216;django-fun&#8217; hosted on it.  The host and port can be left blank if the database is using defaults for them.
The direct insertion can be executed by running the &#8216;main.py&#8217; script  from the command prompt using the
command-line argument <tt class="docutils literal"><span class="pre">database-direct</span></tt>:</p>
<div class="highlight-python"><pre>python main.py database-direct</pre>
</div>
</div>
<div class="section" id="restructuredtext-writer">
<span id="rst-output"></span><h5>restructuredText Writer<a class="headerlink" href="#restructuredtext-writer" title="Permalink to this headline">¶</a></h5>
<p><em>The Sphinx framework must be added to your python installation as described</em> <a class="reference internal" href="index.html#install-sphinx"><em>here</em></a>.</p>
<p>This output format was created for my convenience during development.  If you can figure out how it works, good for you.</p>
</div>
</div>
</div>
</div>
</div>
<div class="section" id="future-data-acquisition">
<h2>Future Data Acquisition<a class="headerlink" href="#future-data-acquisition" title="Permalink to this headline">¶</a></h2>
<div class="toctree-wrapper compound">
<span id="document-excel_caution"></span><div class="section" id="managing-data-acquisition-with-excel">
<h3>Managing Data Acquisition (with Excel)<a class="headerlink" href="#managing-data-acquisition-with-excel" title="Permalink to this headline">¶</a></h3>
<p>Excel, while a powerful tool, is not the best solution to many problems.
The mass collection of data for components in the ATR plant systems is a large
project that will require several man-years of work.  This work demand will require
a combined effort of tens of Engineers if it is to be accomplished in a reasonable time period.
Maintaining data integrity and consistency is at least as important as comprehensive coverage of
the plant.</p>
<p>Excel is not designed to be a collaborative tool, and must be used with extreme care in order to maintain
data integrity. Excel is also unable to properly handle data structures of any significant
complexity (complexity of the kind involved in this project). Following are sections that discuss
Excel&#8217;s limitations and protective measures that can be taken to reduce problem frequency.</p>
<div class="section" id="excel-is-flat">
<h4>Excel is Flat<a class="headerlink" href="#excel-is-flat" title="Permalink to this headline">¶</a></h4>
<p>Some fields for a single component contain multiple instances of the field data type.  For example,
the &#8216;Drawing&#8217; field for a component may have several drawings listed.
For the sake of this document, these kinds of fields will be called multi-fields.
A table follows illustrating this concept:</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="38%" />
<col width="62%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head">Tag ID</th>
<th class="head">Drawings</th>
</tr>
</thead>
<tbody valign="top">
<tr><td>670-M-10</td>
<td>123456; 234567; 345678</td>
</tr>
<tr><td>670-M-11</td>
<td>334567; 445678</td>
</tr>
</tbody>
</table>
</div></blockquote>
<p>Where &#8216;123456&#8217; is an instance of the &#8216;Drawing&#8217; data type (as is &#8216;234567&#8217;, etc.).
670-M-10 has three instances of the &#8216;Drawing&#8217; data type.
670-M-11 has two instances of the &#8216;Drawing&#8217; data type.
The &#8216;Drawings&#8217; title is a multi-field.  Notice the distinction between &#8216;Drawings&#8217; field title
and the &#8216;Drawing&#8217; data type. The &#8216;Drawings&#8217; multi-field can contain multiple instances of the
&#8216;Drawing&#8217; data type.</p>
<p>The natural data structure for a component is tree-shaped and not flat.
Excel, as a glorified table, can only handle tree-shaped data via work-arounds.
There are a few fields that I have identified as multi-fields:</p>
<blockquote>
<div><ul class="simple">
<li>Drawings</li>
<li>Flows (To and From): Mechanical, Electrical, and I &amp; C</li>
<li>Picture Names</li>
</ul>
</div></blockquote>
<p>All fields that have plural titles are potential multi-fields.
The simplest (and best in this case) work-around is to separate instances in a multi-field with a designated delimiter.
Storing data using this method allows the placement of
multiple instances of a data type in a single field or cell. Safely using this delimited approach has two requirements:</p>
<blockquote>
<div><ol class="arabic simple">
<li>An appropriate delimiter must be selected and must be the only delimiter used for all data in the multi-field.
The delimiter must <strong>not</strong> occur in any instance of the multi-field&#8217;s data type.</li>
<li>Avoid extraneous data in the field (including tabs and returns).
The <strong>only</strong> things existing in the field are data type instances and the delimiter.</li>
</ol>
</div></blockquote>
<p>Three examples of <strong>incorrect</strong> data entry are shown below.
&#8216;Flows&#8217; is the multi-field used in these examples.</p>
<blockquote>
<div><ul>
<li><p class="first">Violates rule #1 (comma delimiter occurs in &#8216;Flow&#8217; data type instance)</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="28%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head"><p class="first last">Tag ID</p>
</th>
<th class="head"><p class="first last">Flows (upstream)</p>
</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><p class="first last">670-M-10</p>
</td>
<td><p class="first last">670-M-11,A, 670-M-9</p>
</td>
</tr>
<tr><td><p class="first last">670-M-11,A</p>
</td>
<td><p class="first last">670-M-10</p>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
</li>
<li><p class="first">Violates rule #1 (inconsistent delimiters used)</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="28%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head"><p class="first last">Tag ID</p>
</th>
<th class="head"><p class="first last">Drawings</p>
</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><p class="first last">670-M-10</p>
</td>
<td><p class="first last">123456, 234567; 345678</p>
</td>
</tr>
<tr><td><p class="first last">670-M-11</p>
</td>
<td><p class="first last">334567; 445678</p>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
</li>
<li><p class="first">Violates rule #2 (&#8216;heat exchanger&#8217; text is not part of &#8216;Flow&#8217; data type)</p>
<blockquote>
<div><table border="1" class="docutils">
<colgroup>
<col width="28%" />
<col width="72%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head"><p class="first last">Tag ID</p>
</th>
<th class="head"><p class="first last">Flows (upstream)</p>
</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><p class="first last">670-M-10</p>
</td>
<td><p class="first last">670-M-11,A; 670-M-9</p>
</td>
</tr>
<tr><td><p class="first last">670-M-11,A</p>
</td>
<td><p class="first last">670-M-10 (heat exchanger)</p>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
</li>
</ul>
</div></blockquote>
</div>
<div class="section" id="single-user-documents">
<h4>Single User Documents<a class="headerlink" href="#single-user-documents" title="Permalink to this headline">¶</a></h4>
<ul class="simple">
<li>on spreading the workload</li>
</ul>
<p>Although Excel provides functionality for multiple users to view/edit a workbook
at the same time, this feature is notorious for being unstable and prone to losing data.
I strongly recommend against it.</p>
<p>My recommendation for managing data in single-user-document setting involves the following:</p>
<blockquote>
<div><ul>
<li><p class="first">A master workbook must be maintained.</p>
</li>
<li><p class="first">Generally, data acquisition and construction should take place external to this master workbook.</p>
</li>
<li><p class="first">Completed records will be periodically submitted to the master.</p>
</li>
<li><dl class="first docutils">
<dt>A system/procedures for:</dt>
<dd><ul class="first last simple">
<li>handling corrections to records on the master</li>
<li>identifying duplicate records on the master</li>
<li>Periodic <a class="reference internal" href="index.html#backups"><em>Soft and hard backups</em></a>.</li>
</ul>
</dd>
</dl>
</li>
</ul>
</div></blockquote>
<p>This kind of master-child system has great potential for failure if not designed and executed properly.
Anticipating problems will be very important.
Two problems more likely to be encountered are identified below (with potential causes and possible solutions).
This sort of analysis should be a vital part of designing the data acquisition process:</p>
<blockquote>
<div><table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Problem:</th><td class="field-body"><table class="first docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Description:</th><td class="field-body"><p class="first">Duplicate Records</p>
</td>
</tr>
<tr class="field"><th class="field-name">Discovery:</th><td class="field-body"><p class="first">Search for records that have exactly matching data in one or more fields (e.g. Tag Number)</p>
</td>
</tr>
<tr class="field"><th class="field-name">Causes:</th><td class="field-body"><ul class="first simple">
<li>Record changed after initial submission to master and was resubmitted.</li>
<li>Record(s) submitted twice unknowingly.</li>
</ul>
</td>
</tr>
<tr class="field"><th class="field-name">Solutions:</th><td class="field-body"><ol class="first last arabic simple">
<li>(damage control) Periodic search for duplicate records.</li>
<li>(preventative) Control ID numbers unique to the data acquisition process.
Submitters must acquire ID numbers for each record submitted to the master.
For changes, a submitter must first acquire a copy of the record from the
master workbook with its corresponding control ID. All submissions without a
control ID will be rejected.</li>
</ol>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr class="field"><th class="field-name">Problem:</th><td class="field-body"><table class="first last docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Description:</th><td class="field-body">Relevant data in record fields is missing.</td>
</tr>
<tr class="field"><th class="field-name">Discovery:</th><td class="field-body">Submitter (or other) notices data missing from master.</td>
</tr>
<tr class="field"><th class="field-name">Causes:</th><td class="field-body">Resubmitted a change without including data from current state on master.</td>
</tr>
<tr class="field"><th class="field-name">Solutions:</th><td class="field-body">See solution 2 above.</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div></blockquote>
<p>If unchecked, these (and others) are not problems that might occur - they <em>will</em> occur. A good process architecture can
prevent many problems.</p>
</div>
<div class="section" id="change-tracking-and-backups">
<span id="backups"></span><h4>Change-Tracking and Backups<a class="headerlink" href="#change-tracking-and-backups" title="Permalink to this headline">¶</a></h4>
<p>With the distributed data collection that will be occuring, some form of change-tracking will
be important. This can be as simple as making a nightly copy of the master Excel workbook and placing
archiving it away for future reference (known as a soft backup).
Change tracking allows for some consequence mitigation of human failures.
The time required to gather the data is very valuable.  The master Excel workbook should have at least
one backup of itself on separate computer hardware, protecting against hardware failure. This backup
should be updated regularly (known as a hard backup).</p>
<p>Although it will be difficult to protect against human and hardware failures before the data reaches the master
hub, data collectors should be encouraged to make both hard and soft backups as they assemble data
in the stages leading to submission to the master.</p>
</div>
<div class="section" id="handling-pictures">
<h4>Handling Pictures<a class="headerlink" href="#handling-pictures" title="Permalink to this headline">¶</a></h4>
<p>My (strong) recommendations:</p>
<blockquote>
<div><ul>
<li><dl class="first docutils">
<dt>Every picture must be given a unique filename</dt>
<dd><ul class="first last simple">
<li>Although the name can be made to be something like &#8216;670-M-10_pic1.jpg&#8217; for convenience,
it is quite arbitrary.</li>
</ul>
</dd>
</dl>
</li>
<li><p class="first">All pictures (for the entire plant) should be stored in a single folder.</p>
</li>
<li><dl class="first docutils">
<dt>Picture filenames should only include:</dt>
<dd><ul class="first last simple">
<li>alpha-numeric characters (i.e. &#8216;abcdef...&#8217; and &#8216;0123&#8217;)</li>
<li>underscore: &#8216;_&#8217;</li>
<li>dash: &#8216;-&#8216;</li>
</ul>
</dd>
</dl>
</li>
<li><p class="first">Only store the filenames of a component&#8217;s pictures in Excel.
Pictures should <strong>not</strong> be stored in Excel workbooks.</p>
</li>
<li><p class="first">In an Excel record, do <strong>not</strong> include file paths with the picture names.
Store it in a separate field if desired.</p>
</li>
</ul>
</div></blockquote>
</div>
</div>
<div class="section" id="conclusions">
<h3>Conclusions<a class="headerlink" href="#conclusions" title="Permalink to this headline">¶</a></h3>
<p>The rate of data collection in this project will require significant oversight in order to maintain good data integrity and consistency.
Whether or not Excel is used, I recommend either dedicating a qualified person to data administration/management or hiring one.
From my experience, it is data becomes inconsistent and degrades very easily without a great deal of management. That management
can take two forms:</p>
<blockquote>
<div><ol class="arabic simple">
<li>A well designed collaborative solution (to be designed and maintained by a Data Administrator)</li>
<li>Oversight of a work-around solution (i.e. Excel) by a busy, frustrated data administrator.</li>
</ol>
</div></blockquote>
<p>I believe that investing time and resources in the former will prove more successful. Although requiring greater resource
and time investment up front, option 1 will allow for higher productivity data acquisition.  Data integrity can also be enforced
and assessed properly. Option 1 would most likely involve a standard database backend (e.g. oracle, MySQL, PostgreSQL, etc.) and
a web-based frontend. Good luck!</p>
</div>
</div>
</div>
</div>


          </div>
        </div>
      </div>
      <div class="sphinxsidebar">
        <div class="sphinxsidebarwrapper">
  <h3><a href="#">Table Of Contents</a></h3>
  <ul>
<li class="toctree-l1"><a class="reference internal" href="index.html#document-preparation">Preparing for Migration</a><ul>
<li class="toctree-l2"><a class="reference internal" href="index.html#installing-and-using-python">Installing and Using Python</a><ul>
<li class="toctree-l3"><a class="reference internal" href="index.html#adding-django-to-python">Adding Django to Python</a></li>
<li class="toctree-l3"><a class="reference internal" href="index.html#adding-sphinx-to-python">Adding Sphinx to Python</a></li>
</ul>
</li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="index.html#document-the_conversion_process">The Migration Process</a><ul>
<li class="toctree-l2"><a class="reference internal" href="index.html#configuring-constants-py">Configuring constants.py</a></li>
<li class="toctree-l2"><a class="reference internal" href="index.html#doc-to-docx-conversion">Doc to Docx Conversion</a></li>
<li class="toctree-l2"><a class="reference internal" href="index.html#stripping-the-data">Stripping the Data</a></li>
<li class="toctree-l2"><a class="reference internal" href="index.html#outputting-data-to-other-formats">Outputting Data to Other Formats</a><ul>
<li class="toctree-l3"><a class="reference internal" href="index.html#delimited-file">Delimited File</a></li>
<li class="toctree-l3"><a class="reference internal" href="index.html#xml-files">XML Files</a></li>
<li class="toctree-l3"><a class="reference internal" href="index.html#direct-database-insertion">Direct Database Insertion</a></li>
<li class="toctree-l3"><a class="reference internal" href="index.html#restructuredtext-writer">restructuredText Writer</a></li>
</ul>
</li>
</ul>
</li>
</ul>
<ul>
<li class="toctree-l1"><a class="reference internal" href="index.html#document-excel_caution">Managing Data Acquisition (with Excel)</a><ul>
<li class="toctree-l2"><a class="reference internal" href="index.html#excel-is-flat">Excel is Flat</a></li>
<li class="toctree-l2"><a class="reference internal" href="index.html#single-user-documents">Single User Documents</a></li>
<li class="toctree-l2"><a class="reference internal" href="index.html#change-tracking-and-backups">Change-Tracking and Backups</a></li>
<li class="toctree-l2"><a class="reference internal" href="index.html#handling-pictures">Handling Pictures</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="index.html#conclusions">Conclusions</a></li>
</ul>

<div id="searchbox" style="display: none">
  <h3>Quick search</h3>
    <form class="search" action="search.html" method="get">
      <input type="text" name="q" size="18" />
      <input type="submit" value="Go" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    <p class="searchtip" style="font-size: 90%">
    Enter search terms or a module, class or function name.
    </p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>
      <div class="clearer"></div>
    </div>
    <div class="related">
      <h3>Navigation</h3>
      <ul>
        <li><a href="#">RWC Converter v1.0 documentation</a> &raquo;</li> 
      </ul>
    </div>
    <div class="footer">
        &copy; Copyright 2011, Robert Carlsen.
      Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
    </div>
  </body>
</html>